﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using DevExpress.Web.Data;
using DevExpress.Web.ASPxGridView;
using System.IO;
using BASE;

public partial class Admin_dssinhvien : System.Web.UI.Page
{
    static string FileName = "d:/sinhvien.xls";
    static string Lop = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        //check quyen vao trang

        if (Utils.getIntSession("userid") == 0)
        {
            Response.Redirect("/admin/login.aspx");
        }
        //busMenu busM = new busMenu();
        //if (!busM.checkQuyenVaoTrang(Utils.getIntSession("userid"), Constants.ADMINY_DSYKIEN))
        //{
        //    Response.Redirect("/admin/");
        //}

        rptPager.ItemCommand += new RepeaterCommandEventHandler(rptPager_ItemCommand);
        grid.RowDataBound += new GridViewRowEventHandler(grid_RowDataBound);
        if (!IsPostBack)
        {

        }
    }

    void grid_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            for (int i = 0; i < e.Row.Cells.Count; i++)
            {
                if (e.Row.Cells[i].Text == "" || e.Row.Cells[i].Text == string.Empty || e.Row.Cells[i].Text == "&nbsp;")
                {
                    e.Row.Cells[i].CssClass = "rong";
                }
            }
        }
    }

    void rptPager_ItemCommand(object source, RepeaterCommandEventArgs e)
    {
        string cmd = e.CommandName;
        if (cmd == "Chuyen")
        {
            bindata(FileName, e.CommandArgument.ToString());
            Lop = e.CommandArgument.ToString();
        }
    }



    void bindata(string filepath, string sheetname)
    {
        DataTable dt = new DataTable();
        dt = ImportExcelXLS(filepath, true, sheetname);
        grid.DataSource = dt;
        grid.DataBind();
    }
    DataTable bindatarepe(string FileName)
    {
        DataTable dt = ListSheetInExcel(FileName);
        if (dt.Rows.Count == 0)
        {
            return null;
        }
        rptPager.DataSource = dt;
        rptPager.DataBind();
        return dt;
    }
    public DataTable ListSheetInExcel(string filePath)
    {
        OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
        String strExtendedProperties = String.Empty;
        sbConnection.DataSource = filePath;
        if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xls")//for 97-03 Excel file
        {
            sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
            strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
        }
        else if (Path.GetExtension(filePath).Equals(".xlsx"))  //for 2007 Excel file
        {
            sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
            strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
        }


        sbConnection.Add("Extended Properties", strExtendedProperties);
        List<string> listSheet = new List<string>();
        DataTable dtSheet = new DataTable();
        DataTable dt = new DataTable();
        using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
        {
            conn.Open();
            dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            List<DataRow> lst = new List<DataRow>();
            foreach (DataRow drSheet in dtSheet.Rows)
            {
                if (!drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
                {
                    lst.Add(drSheet);
                }
                drSheet["TABLE_NAME"] = drSheet["TABLE_NAME"].ToString().Replace("$", "").Replace("'", "");
            }
            foreach (DataRow item in lst)
            {
                dtSheet.Rows.Remove(item);
            }
            conn.Close();
        }


        return dtSheet;
    }

    public static DataTable ImportExcelXLS(string FileName, bool hasHeaders, string sheetname)
    {
        string HDR = hasHeaders ? "Yes" : "No";
        string strConn;
        if (FileName.Substring(FileName.LastIndexOf('.')).ToLower() == ".xlsx")
        {
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
        }
        else
        {
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
        }

        DataSet output = new DataSet();
        DataTable dt = new DataTable();
        using (OleDbConnection conn = new OleDbConnection(strConn))
        {
            try
            {
                conn.Open();
                String a = "SELECT * FROM [" + sheetname + "$A6:BB] where masinhvien is not null";
                OleDbCommand cmd = new OleDbCommand(a, conn);
                cmd.CommandType = CommandType.Text;
                OleDbDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);


            }
            catch (OleDbException e)
            {
                HttpContext.Current.Response.Write("Loi sai cau chuc");
            }
            catch (Exception)
            {
                conn.Close();
                throw;
            }
            conn.Close();
        }
        return dt;
    }
    protected void btn_up_Click(object sender, EventArgs e)
    {
        List<WebControl> lst = new List<WebControl>();
        lst.Add(file);
        string check = Utils.checkDataType(lst);
        if (check != "")
        {
            lb_me.Text = check;
            return;
        }
        else
        {
            lb_me.Text = "";
        }
        if (!Directory.Exists(MapPath(Constants.DATAFOLDER)))
        {
            Directory.CreateDirectory(MapPath(Constants.DATAFOLDER));
        }
        file.SaveAs(MapPath(Constants.DATAFOLDER + Utils.getStringSession("userid") + Path.GetExtension(file.FileName)));
        FileName = MapPath(Constants.DATAFOLDER + Utils.getStringSession("userid") + Path.GetExtension(file.FileName));
        DataTable dt = bindatarepe(FileName);
        if (dt.Rows.Count > 0)
        {

            bindata(FileName, dt.Rows[0]["TABLE_NAME"].ToString());
            Lop = dt.Rows[0]["TABLE_NAME"].ToString();
        }
    }
    protected void btn_luu_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        dt = ImportExcelXLS(FileName, true, Lop);
        entSinhVien entSV = new entSinhVien();
        foreach (DataRow item in dt.Rows)
        {
            entSV = new entSinhVien();
            entSV.MaSinhVien = item["MaSinhVien"].ToString();
            string Ten = item["hoten"].ToString().Trim();
            string[] splitStrings = Ten.Split(' ');
            entSV.Ten = splitStrings[splitStrings.Length - 1];
            entSV.Ho = Ten.Substring(0, Ten.Length - entSV.Ten.Length);

            entSV.GioiTinh = 0;
            if (item["gioitinh"].ToString().ToUpper() == "NAM")
            {
                entSV.GioiTinh = 1;
            }
            entSV.NoiSinh = item["noisinh"].ToString();
            entSV.HoKhau = item["HoKhauThuongChu"].ToString();
            entSV.DanToc = getDanToc(item["dantoc"].ToString());
            entSV.TonGiao = item["tongiao"].ToString();
            entSV.HoTenBo = item["hotenbo"].ToString();
            entSV.NNBo = item["nghenghiepbo"].ToString();
            entSV.HoTenMe = item["hotenme"].ToString();
            entSV.NNMe = item["nghenghiepme"].ToString();
            entSV.NamTN12 = item["namtnthpt"].ToString();
            entSV.SoHieuBang12 = item["sohieubangtn"].ToString();
            entSV.CMTND = item["cmtnd"].ToString();
            entSV.NgayCap = item["ngayCap"].ToString();
            entSV.DiaChi = item["DiachiGiaDinh"].ToString();
            entSV.DiaChiHT = item["diachihientai"].ToString();
            entSV.DienThoai = item["dienthoai"].ToString();
            entSV.Email = item["email"].ToString();
            entSV.GhiChu = item["ghichu"].ToString();
        }
    }
    protected int getDanToc(string dt)
    {
        switch (dt.ToUpper())
        {
            case "KINH":
                return 1;
            case "TÀY":
                return 2;
            case "THÁI":
                return 3;
            case "MƯỜNG":
                return 4;
            case "KHMER":
                return 5;
            case "HOA":
                return 6;
            case "NÙNG":
                return 7;
            case "MÔNG":
                return 8;
            case "DAO":
                return 9;
            case "GIA RAI":
                return 10;
            case "Ê ĐÊ":
                return 11;
            case "BA NA":
                return 12;
            case "SÁN CHAY":
                return 13;
            case "CHĂM":
                return 14;
            case "CƠ HO":
                return 15;
            case "XƠ ĐĂNG":
                return 16;
            case "SÁN DÌU":
                return 17;
            case "HRÊ":
                return 18;
            case "RAGLAY":
                return 19;
            case "MNÔNG":
                return 20;
            case "THỔ":
                return 21;
            case "XTIÊNG":
                return 22;
            case "KHƠ MÚ":
                return 23;
            case "BRU VÂN KIỀU":
                return 24;
            case "CƠ TU":
                return 25;
            case "GIÁY":
                return 26;
            case "TÀ ÔI":
                return 27;
            case "MẠ":
                return 28;
            case "GIẺ-TRIÊNG":
                return 29;
            case "CO":
                return 30;
            case "CHƠ RO":
                return 31;
            case "XINH MUN":
                return 32;
            case "HÀ NHÌ":
                return 33;
            case "CHU RU":
                return 34;
            case "LÀO":
                return 35;
            case "LA CHÍ":
                return 36;
            case "KHÁNG":
                return 37;
            case "PHÚ LÁ":
                return 38;
            case "LA HỦ":
                return 39;
            case "LA HA":
                return 40;
            case "PÀ THẺN":
                return 41;
            case "LỰ":
                return 42;
            case "NGÁI":
                return 43;
            case "CHỨT":
                return 44;
            case "MẢNG":
                return 45;
            case "LÔ LÔ":
                return 46;
            case "CƠ LAO":
                return 47;
            case "BỐ Y":
                return 48;
            case "CỐNG":
                return 49;
            case "SI LA":
                return 50;
            case "PU PÉO":
                return 51;
            case "RƠ MĂM":
                return 52;
            case "BRÂU":
                return 53;
            case "Ơ ĐU":
                return 54;
           
            default:
                return 0;

        }
        return 0;
    }

}